07. Working with rows

SELECT

The SELECT statement reads rows from a table.

SELECT column-names FROM table-name

To select all columns, substitute column-names with *, like this

SELECT * FROM table-name

SELECT with WHERE

WHERE is used to limit the number of rows.
The WHERE clause filters for rows that meet certain criteria.
WHERE is followed by a condition that returns either true or false.

SELECT column-names FROM table-name WHERE condition

ORDER BY

SELECT returns records in no particular order, use ORDER BY to ensure a specific order.
ORDER BY allows sorting by one or more columns.
Rows can be returned in ascending or descending order.

SELECT column-names FROM table-name WHERE condition ORDER BY column-names

JOIN

A SQL JOIN combines records from two tables. A query can contain zero, one, or more JOIN operations.

Different types of JOINs

(INNER) JOIN: Select records that have matching values in both tables.
LEFT (OUTER) JOIN: Select records from the first (left-most) table with matching right table records.
RIGHT (OUTER) JOIN: Select records from the second (right-most) table with matching left table records.
FULL (OUTER) JOIN: Selects all records that match either left or right table records.

INNER JOIN

SELECT column-names FROM table-name1 INNER JOIN table-name2 ON column-name1 = column-name2 WHERE condition

The INNER keyword is optional.

LEFT JOIN

LEFT JOIN performs a join starting with the first (left-most) table and then any matching second (right-most) table records.

SELECT column-names FROM table-name1 LEFT JOIN table-name2 ON column-name1 = column-name2 WHERE condition

RIGHT JOIN

RIGHT JOIN performs a join starting with the second (right-most) table and then any matching first (left-most) table records.

SELECT column-names FROM table-name1 RIGHT JOIN table-name2 ON column-name1 = column-name2 WHERE condition

FULL JOIN

FULL JOIN returns all matching records from both tables whether the other table matches or not. It can potentially return very large datasets.

SELECT column-names FROM table-name1 FULL JOIN table-name2 ON column-name1 = column-name2 WHERE condition